﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CFM.DTO;
using CFM.DAL;
namespace CFM.API
{
    class SalesSatisticAPI
    {
        public DataHelper _helper = null;
        public SalesSatisticAPI()
        {
            _helper = new DataHelper();
        }
        public List<SalesSatistic> GetALL()
        {
           // string sql = "select ProductId, ProductName,UnitName,Quantity,(Quantity*SalePrice) as Turnover,(Quantity*BuyPrice) as Capital,Quantity*(SalePrice-BuyPrice)as Disparity FROM PRODUCT AS PR,UNIT AS Un WHERE PR.Unit1Id=Un.UNITID";
            string sql = "select bd.Productid,bd.ProductName,u.UnitName,sum(bd.Quantity) as Quantity,sum(bd.DetailTotal) as Turnover,sum(p.BuyPrice*bd.Quantity) as Capital,sum(bd.DetailTotal-(p.BuyPrice*bd.Quantity))as Disparity"
                         + " FROM BillDetail bd,Bill b,Product p, Unit u ,Categories ct "
                         + " where bd.productid = p.ProductId and bd.BillId=b.BillId and u.UnitId = bd.UnitId and p.CategoryId = ct.CategoryId"
                         +   " group by bd.Productid,bd.ProductName,u.UnitName";
            SqlDataReader dr = _helper.ExcuteDataReader(sql, null);
            return _helper.MapDataReaderToObject<SalesSatistic>(dr);
        }


        public List<SalesSatistic> SalesSatistic(string cateId, string productID, DateTime DateFrom, DateTime DateTo)
        {
            string sql = "select bd.Productid,bd.ProductName,u.UnitName,sum(bd.Quantity) as Quantity,sum(bd.DetailTotal) as Turnover,sum(p.BuyPrice*bd.Quantity) as Capital,sum(bd.DetailTotal-(p.BuyPrice*bd.Quantity))as Disparity"
                         + " FROM BillDetail bd,Bill b,Product p, Unit u "
                         + " where bd.productid = p.ProductId and bd.BillId=b.BillId and u.UnitId = bd.UnitId"
                         + " and (@cateid = '-1' or p.CategoryId = @cateid)"
                         + " and (@productID = '-1' or bd.ProductID = @productID)"
                         +" and CONVERT(date, Billdate) between CONVERT(date, @dtfrom) and CONVERT(date, @dtto)"
                         + " group by bd.Productid,bd.ProductName,u.UnitName";

            //"select * from Product  where"
            //        + " (@cateid = -1 or CateId = @cateid)"
            //        + " and (@productName = '-1' or Name like '%'+@productName+'%')"
            //        + " and ((@pricefrom = -1 and @priceto != -1 and Price <= @priceto)"
            //        + " or (@pricefrom != -1 and @priceto = -1 and Price >= @pricefrom)"
            //        + " or (@pricefrom != -1 and @priceto != -1 and Price between @pricefrom and @priceto)"
            //        + "or (@pricefrom = -1 and @priceto = -1))"
            //        + " and Amount > 0 order by CreateDate";
            SqlParameter[] prs = new SqlParameter[4];
            prs[0] = new SqlParameter("@cateid", SqlDbType.Char);
            prs[1] = new SqlParameter("@productID", SqlDbType.Char);
            prs[2] = new SqlParameter("@dtfrom", SqlDbType.DateTime);
            prs[3] = new SqlParameter("@dtto", SqlDbType.DateTime);


            prs[0].Value = cateId != string.Empty ? cateId : "-1";
            prs[1].Value = productID != string.Empty ? productID : "-1";
            prs[2].Value = DateFrom;
            prs[3].Value = DateTo;

            SqlDataReader dr = _helper.ExcuteDataReader(sql,prs);
            return _helper.MapDataReaderToObject<SalesSatistic>(dr);
        }
    }
}
